﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Threading;

public partial class EditDocment : System.Web.UI.Page
{
    List<_Article> document_article = new List<_Article>();
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        if (Request.IsAuthenticated == false)
        {
            Response.Redirect("login.aspx");
        }

        if (Request.IsAuthenticated)
        {
            string n = "";
            SqlCommand sCmd = new SqlCommand("SELECT name FROM USERS WHERE id=@id", conn);
            sCmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = User.Identity.Name;
            conn.Open();
            SqlDataReader dr = sCmd.ExecuteReader();
            while (dr.Read())
            {
                string name = Convert.ToString(dr[0].ToString());
                n = name;
            }
            dr.Close();
            conn.Close();

            WelcomeBackMessage.Text = "Welcome back, " + n + "!";

            AuthenticatedHeaderPanel.Visible = true;
            AuthenticatedMessagePanel.Visible = true;
            AuthenticatedMenuPanel.Visible = true;

            if (!IsPostBack)
            {
                SetData();
            }
        }
        else
        {
            AuthenticatedHeaderPanel.Visible = false;
            AuthenticatedMessagePanel.Visible = false;
            AuthenticatedMenuPanel.Visible = false;
        }
    }

    private void SetData()
    {
        
        Response.Write(lbl_doc_id.Text = Session["Doc_ID"].ToString());

        List<int> document_id_company = new List<int>();
        List<int> document_id_client = new List<int>();
        int id_user = Convert.ToInt32(User.Identity.Name);
        lbl_user_id.Text = id_user.ToString();

        //_User.EditUser(lbl_user_id.Text);
        //lbl_user.Text = _User.EditUser(lbl_user_id.Text)._Name + " " + _User.EditUser(lbl_user_id.Text)._SecondName;

        _Invoice.EditDocument(lbl_doc_id.Text);
        tb_doc_number.Text = _Invoice.EditDocument(lbl_doc_id.Text).document_number;
        tb_doc_date.Text = _Invoice.EditDocument(lbl_doc_id.Text).document_date.ToString("yyyy.MM.dd");
        tb_doc_pay_date.Text = _Invoice.EditDocument(lbl_doc_id.Text).document_payment_date.ToString("yyyy.MM.dd");

        #region COMPANY
        //atlasam visas kompānijas
        conn.Open();
        SqlCommand selCmd_company = new SqlCommand("SELECT id_company FROM user_company WHERE id_user=@id_user AND client=@client", conn);
        selCmd_company.Parameters.Add(new SqlParameter("@id_user", System.Data.SqlDbType.Int)).Value = id_user;
        selCmd_company.Parameters.Add(new SqlParameter("@client", System.Data.SqlDbType.VarChar)).Value = "false";
        SqlDataReader dr = selCmd_company.ExecuteReader();
        while (dr.Read())
        {
            document_id_company.Add(Convert.ToInt32(dr["id_company"]));
        }
        dr.Close();
        conn.Close();
        foreach (int id_company in document_id_company)
        {
            conn.Open();
            SqlCommand selCmd_company1 = new SqlCommand("SELECT id, company_name FROM company WHERE id=@id", conn);
            selCmd_company1.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_company;
            SqlDataReader dr1 = selCmd_company1.ExecuteReader();
            while (dr1.Read())
            {
                ListItem item = new ListItem();
                item.Value = dr1["id"].ToString();
                item.Text = dr1["company_name"].ToString();
                Drop_company_from.Items.Add(item);
            }
            dr1.Close();
            conn.Close();
        }
        Drop_company_from.Items.Insert(0, "--Select--");
        //atlasam konkrēto kompāniju kura ir pie dokumenta
        string id_company_by = _Invoice.EditDocument(lbl_doc_id.Text).id_company_issued_by.ToString();
        conn.Open();
        SqlCommand sel_company_by = new SqlCommand("SELECT company_name FROM company WHERE id=@id", conn);
        sel_company_by.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_company_by;
        SqlDataReader dr2 = sel_company_by.ExecuteReader();
        while (dr2.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_company_by;
            item.Text = dr2["company_name"].ToString();
            Drop_company_from.Items.Insert(0, item);
        }
        dr2.Close();
        conn.Close();
        //atlasam visas kompānijas adreses
        conn.Open();
        SqlCommand sel_company_by_address = new SqlCommand("SELECT id, address FROM company_address WHERE id_company=@company_id", conn);
        sel_company_by_address.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Drop_company_from.SelectedValue;
        SqlDataReader dr3 = sel_company_by_address.ExecuteReader();
        while (dr3.Read())
        {
            ListItem item = new ListItem();
            item.Value = dr3["id"].ToString();
            item.Text = dr3["address"].ToString();
            Drop_address_company_from.Items.Add(item);
        }
        dr3.Close();
        conn.Close();
        Drop_address_company_from.Items.Insert(0, "--Select--");
        //atlasam konkrēto adresi kura ir pie dokumnta
        string id_company_by_address = _Invoice.EditDocument(lbl_doc_id.Text).id_address_company_by.ToString();
        conn.Open();
        SqlCommand sel_company_by_address1 = new SqlCommand("SELECT address FROM company_address WHERE id=@id", conn);
        sel_company_by_address1.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_company_by_address;
        SqlDataReader dr4 = sel_company_by_address1.ExecuteReader();
        while (dr4.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_company_by_address;
            item.Text = dr4["address"].ToString();
            Drop_address_company_from.Items.Insert(0,item);
        }
        dr4.Close();
        conn.Close();
        //atlasam visas kompānijas bankas
        List<int> bank = new List<int>();
        conn.Open();
        SqlCommand sel_company_by_bank = new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
        sel_company_by_bank.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Drop_company_from.SelectedValue;
        SqlDataReader dr5 = sel_company_by_bank.ExecuteReader();
        while (dr5.Read())
        {
            bank.Add(Convert.ToInt32(dr5["id_bank"]));
        }
        dr5.Close();
        conn.Close();
        conn.Open();
        foreach (var bank_id in bank)
        {
            SqlCommand selCmd = new SqlCommand("SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bank_id;
            SqlDataReader dr6 = selCmd.ExecuteReader();
            while (dr6.Read())
            {
                ListItem item = new ListItem();
                item.Value = dr6["id"].ToString();
                item.Text = dr6["bank_name"].ToString();
                Drop_BANK_company_from.Items.Add(item);
            }
            dr6.Close();
        }
        conn.Close();
        Drop_BANK_company_from.Items.Insert(0, "--Select--");
        //atlasam konkrēto banku kura ir pie dokumnta
        string id_company_by_bank = _Invoice.EditDocument(lbl_doc_id.Text).id_bank_company_by.ToString();
        conn.Open();
        SqlCommand sel_company_by_bank_doc = new SqlCommand("SELECT bank_name FROM bank WHERE id=@id", conn);
        sel_company_by_bank_doc.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_company_by_bank;
        SqlDataReader dr7 = sel_company_by_bank_doc.ExecuteReader();
        while (dr7.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_company_by_bank;
            item.Text = dr7["bank_name"].ToString();
            Drop_BANK_company_from.Items.Insert(0, item);
        }
        dr7.Close();
        conn.Close();
        //atlasam visus konta nummurus konkrētai bankai
        List<int> company_bank_ID = new List<int>();
        conn.Open();
        SqlCommand sel_company_by_bank_ID = new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
        sel_company_by_bank_ID.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = Drop_BANK_company_from.SelectedValue;
        sel_company_by_bank_ID.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Drop_company_from.SelectedValue;
        SqlDataReader dr8 = sel_company_by_bank_ID.ExecuteReader();
        while (dr8.Read())
        {
            company_bank_ID.Add(Convert.ToInt32(dr8["id"]));
        }
        dr8.Close();
        conn.Close();
        conn.Open();
        foreach (var com_bank_ID in company_bank_ID)
        {
            SqlCommand sel_company_by_bank_account = new SqlCommand("SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id", conn);
            sel_company_by_bank_account.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).Value = com_bank_ID;
            SqlDataReader dr9 = sel_company_by_bank_account.ExecuteReader();
            while (dr9.Read())
            {
                ListItem item = new ListItem();
                item.Value = dr9["id_account_number"].ToString();
                item.Text = dr9["account_number"].ToString();
                Drop_ACCOUNT_company_from.Items.Add(item);
            }
            dr9.Close();
        }
        conn.Close();
        Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
        //atlasam konkrēto konta mummuru kurš ir pie dokumenta
        string id_company_by_account = _Invoice.EditDocument(lbl_doc_id.Text).id_account_company_by.ToString();
        conn.Open();
        SqlCommand sel_company_by_account = new SqlCommand("SELECT account_number FROM account_number WHERE id_account_number=@id", conn);
        sel_company_by_account.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_company_by_account;
        SqlDataReader drr = sel_company_by_account.ExecuteReader();
        while (drr.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_company_by_account;
            item.Text = drr["account_number"].ToString();
            Drop_ACCOUNT_company_from.Items.Insert(0, item);
        }
        drr.Close();
        conn.Close();
#endregion  

        #region CLIENT
        conn.Open();
        SqlCommand selCmd_client = new SqlCommand("SELECT id_company FROM user_company WHERE id_user=@id_user AND client=@client", conn);
        selCmd_client.Parameters.Add(new SqlParameter("@id_user", System.Data.SqlDbType.Int)).Value = id_user;
        selCmd_client.Parameters.Add(new SqlParameter("@client", System.Data.SqlDbType.VarChar)).Value = "true";
        SqlDataReader drr1 = selCmd_client.ExecuteReader();
        while (drr1.Read())
        {
            document_id_client.Add(Convert.ToInt32(drr1["id_company"]));
        }
        drr1.Close();
        conn.Close();
        foreach (int id_company in document_id_client)
        {
            conn.Open();
            SqlCommand selCmd_client1 = new SqlCommand("SELECT id, company_name FROM company WHERE id=@id", conn);
            selCmd_client1.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_company;
            SqlDataReader drr2 = selCmd_client1.ExecuteReader();
            while (drr2.Read())
            {
                ListItem item = new ListItem();
                item.Value = drr2["id"].ToString();
                item.Text = drr2["company_name"].ToString();
                Drop_client_to.Items.Add(item);
            }
            drr2.Close();
            conn.Close();
        }
        Drop_client_to.Items.Insert(0, "--Select--");
        //atlasam konkrēto klientu kura ir pie dokumenta
        string id_client = _Invoice.EditDocument(lbl_doc_id.Text).id_client_issued_to.ToString();
        conn.Open();
        SqlCommand sel_company_to = new SqlCommand("SELECT company_name FROM company WHERE id=@id", conn);
        sel_company_to.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_client;
        SqlDataReader drr3 = sel_company_to.ExecuteReader();
        while (drr3.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_client;
            item.Text = drr3["company_name"].ToString();
            Drop_client_to.Items.Insert(0, item);
        }
        drr3.Close();
        conn.Close();
        //Atlasam visas klienta adreses
        conn.Open();
        SqlCommand sel_client_to_address = new SqlCommand("SELECT id, address FROM company_address WHERE id_company=@company_id", conn);
        sel_client_to_address.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Drop_client_to.SelectedValue;
        SqlDataReader drr4 = sel_client_to_address.ExecuteReader();
        while (drr4.Read())
        {
            ListItem item = new ListItem();
            item.Value = drr4["id"].ToString();
            item.Text = drr4["address"].ToString();
            Drop_address_client_to.Items.Add(item);
        }
        drr4.Close();
        Drop_address_client_to.Items.Insert(0, "--Select--");
        conn.Close();
        //Atlasam konkrēto adresi kura ir pie dokumenta
        string id_client_to_address = _Invoice.EditDocument(lbl_doc_id.Text).id_address_client_to.ToString();
        conn.Open();
        SqlCommand sel_client_to_address1 = new SqlCommand("SELECT address FROM company_address WHERE id=@id", conn);
        sel_client_to_address1.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_client_to_address;
        SqlDataReader drr5 = sel_client_to_address1.ExecuteReader();
        while (drr5.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_client_to_address;
            item.Text = drr5["address"].ToString();
            Drop_address_client_to.Items.Insert(0, item);
        }
        drr5.Close();
        conn.Close();
        //Atlasam visas klienta bankas
        List<int> client_bank = new List<int>();
        conn.Open();
        SqlCommand sel_client_to_bank = new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
        sel_client_to_bank.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Drop_client_to.SelectedValue;
        SqlDataReader drr6 = sel_client_to_bank.ExecuteReader();
        while (drr6.Read())
        {
            client_bank.Add(Convert.ToInt32(drr6["id_bank"]));
        }
        drr6.Close();
        conn.Close();
        conn.Open();
        foreach (var bank_id in client_bank)
        {
            SqlCommand selCmd = new SqlCommand("SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bank_id;
            SqlDataReader drr7 = selCmd.ExecuteReader();
            while (drr7.Read())
            {
                ListItem item = new ListItem();
                item.Value = drr7["id"].ToString();
                item.Text = drr7["bank_name"].ToString();
                Drop_BANK_client_to.Items.Add(item);
            }
            drr7.Close();
        }
        Drop_BANK_client_to.Items.Insert(0, "--Select--");
        conn.Close();
        //Atlasam konkrēto banku kura ir pie dokumenta
        string id_client_to_bank = _Invoice.EditDocument(lbl_doc_id.Text).id_bank_client_to.ToString();
        conn.Open();
        SqlCommand sel_client_to_bank_doc = new SqlCommand("SELECT bank_name FROM bank WHERE id=@id", conn);
        sel_client_to_bank_doc.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_client_to_bank;
        SqlDataReader drr8 = sel_client_to_bank_doc.ExecuteReader();
        while (drr8.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_client_to_bank;
            item.Text = drr8["bank_name"].ToString();
            Drop_BANK_client_to.Items.Insert(0, item);
        }
        drr8.Close();
        conn.Close();
        //Atlasam visus konta nummurus konkrētajai bankai
        List<int> client_bank_ID = new List<int>();
        conn.Open();
        SqlCommand sel_client_bank_ID = new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
        sel_client_bank_ID.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = Drop_BANK_client_to.SelectedValue;
        sel_client_bank_ID.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = Drop_client_to.SelectedValue;
        SqlDataReader drr9 = sel_client_bank_ID.ExecuteReader();
        while (drr9.Read())
        {
            client_bank_ID.Add(Convert.ToInt32(drr9["id"]));
        }
        drr9.Close();
        conn.Close();
        foreach (var cl_bank_ID in client_bank_ID)
        {
            conn.Open();
            SqlCommand sel_client_to_bank_account = new SqlCommand("SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id ", conn);
            sel_client_to_bank_account.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).Value = cl_bank_ID;
            SqlDataReader drr10 = sel_client_to_bank_account.ExecuteReader();
            while (drr10.Read())
            {
                ListItem item = new ListItem();
                item.Value = drr10["id_account_number"].ToString();
                item.Text = drr10["account_number"].ToString();
                Drop_ACCOUNT_client_to.Items.Add(item);
            }
            drr10.Close();
            conn.Close();
        }
        Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
        //Atlasam konkrēto konta nummuru kurš ir pie dokumenta
        string id_client_to_account = _Invoice.EditDocument(lbl_doc_id.Text).id_account_client_to.ToString();
        conn.Open();
        SqlCommand sel_client_to_account = new SqlCommand("SELECT account_number FROM account_number WHERE id_account_number=@id", conn);
        sel_client_to_account.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id_client_to_account;
        SqlDataReader drr11 = sel_client_to_account.ExecuteReader();
        while (drr11.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_client_to_account;
            item.Text = drr11["account_number"].ToString();
            Drop_ACCOUNT_client_to.Items.Insert(0, item);
        }
        drr11.Close();
        conn.Close();
#endregion

        //DOCUMENT TYPE
        conn.Open();
        SqlCommand sel_document_type = new SqlCommand("SELECT id, type_name FROM document_type", conn);
        SqlDataReader drrr = sel_document_type.ExecuteReader();
        while (drrr.Read())
        {
            ListItem item = new ListItem();
            item.Value = drrr["id"].ToString();
            item.Text = drrr["type_name"].ToString();
            Drop_doc_type.Items.Add(item);
        }
        drrr.Close();
        conn.Close();
        Drop_doc_type.Items.Insert(0, "--Select--");
        //atlasam DOCUMENT TYPE dokumentam
        string id_doc_type = _Invoice.EditDocument(lbl_doc_id.Text).id_document_type.ToString();
        conn.Open();
        SqlCommand sel_doc_type = new SqlCommand("SELECT type_name FROM document_type WHERE id=@id", conn);
        sel_doc_type.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_doc_type;
        SqlDataReader drrr1 = sel_doc_type.ExecuteReader();
        while (drrr1.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_doc_type;
            item.Text = drrr1["type_name"].ToString();
            Drop_doc_type.Items.Insert(0, item);
        }
        drrr1.Close();
        conn.Close();
        
        //PAYMENT TYPE
        conn.Open();
        SqlCommand sel_payment_type = new SqlCommand("SELECT id, type_name FROM payment_type", conn);
        SqlDataReader drrr2 = sel_payment_type.ExecuteReader();
        while (drrr2.Read())
        {
            ListItem item = new ListItem();
            item.Value = drrr2["id"].ToString();
            item.Text = drrr2["type_name"].ToString();
            Drop_pay_type.Items.Add(item);
        }
        drrr2.Close();
        conn.Close();
        Drop_pay_type.Items.Insert(0, "--Select--");
        //atlasam PAYMENT TYPE dokumentam
        string id_pay_type = _Invoice.EditDocument(lbl_doc_id.Text).id_document_type.ToString();
        conn.Open();
        SqlCommand sel_pay_type = new SqlCommand("SELECT type_name FROM payment_type WHERE id=@id", conn);
        sel_pay_type.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.NVarChar)).Value = id_pay_type;
        SqlDataReader drrr3 = sel_pay_type.ExecuteReader();
        while (drrr3.Read())
        {
            ListItem item = new ListItem();
            item.Value = id_pay_type;
            item.Text = drrr3["type_name"].ToString();
            Drop_pay_type.Items.Insert(0,item);
        }
        drrr3.Close();
        conn.Close();

        tb_delivery_place.Text = _Invoice.EditDocument(lbl_doc_id.Text).place_of_delivery;

        article();
    }

    private void article()
    {
        string document_id = lbl_doc_id.Text;
        document_article = _Article.SlectArticle(document_id);
        article_list.DataSource = document_article;
        article_list.DataBind();
        Response.Clear();
    }

    //private string Sort
    //{
    //    get
    //    {
    //        return String.Concat(this.SortExpression, (this.SortDirection == SortDirection.Ascending) ? " DESC" : " ASC");
    //    }
    //}
    //private SortDirection SortDirection
    //{
    //    get
    //    {
    //        if (ViewState["SortDirection"] == null)
    //        {
    //            ViewState["SortDirection"] = SortDirection.Descending;
    //        }

    //        return (SortDirection)ViewState["SortDirection"];
    //    }
    //    set { ViewState["SortDirection"] = value; }
    //}
    //private string SortExpression
    //{
    //    get
    //    {
    //        if (ViewState["SortExpression"] == null)
    //        {
    //            ViewState["SortExpression"] = "ID";
    //        }

    //        return ViewState["SortExpression"].ToString();
    //    }
    //    set { ViewState["SortExpression"] = value; }
    //}

    //protected void article_list_Sorting(object sender, ListViewSortEventArgs e)
    //{
    //    if (this.SortExpression.Equals(e.SortExpression))
    //    {
    //        this.SortDirection = (this.SortDirection == SortDirection.Descending)
    //              ? SortDirection.Ascending
    //              : SortDirection.Descending;
    //    }
    //    else
    //    {
    //        this.SortDirection = SortDirection.Descending;
    //    }
    //    this.SortExpression = e.SortExpression;
    //    this.SetData();
    //}

    protected void article_list_ItemCommand(object sender, ListViewCommandEventArgs e)
    {
        if (e.CommandName == "Insert_article")
        {
            string document_ID = lbl_doc_id.Text;
            var art_name = e.Item.FindControl("tb_name_insert") as TextBox;
            var art_count = e.Item.FindControl("tb_count_insert") as TextBox;
            var art_price = e.Item.FindControl("tb_price_insert") as TextBox;

            _Article.Insert(document_ID, art_name.Text, art_count.Text, art_price.Text);
            Response.Redirect("EditDocment.aspx");
        }
        if (e.CommandName == "Update_article")
        {
            var article_ID = e.Item.FindControl("id_articleLabel") as Label;
            var document_ID = e.Item.FindControl("id_documentLabel") as Label;
            var art_name = e.Item.FindControl("tb_name") as TextBox;
            var art_count = e.Item.FindControl("tb_count") as TextBox;
            var art_price = e.Item.FindControl("tb_price") as TextBox;

            _Article.Update(article_ID.Text, document_ID.Text, art_name.Text, art_count.Text, art_price.Text);
            article_list.EditIndex = -1;
            article();
        }
        if (e.CommandName == "Cancel_article")
        {
            Response.Redirect("EditDocment.aspx");
        }
        if (e.CommandName == "Delete")
        {
            var article_ID = e.Item.FindControl("id_articleLabel") as Label;
            //_Article.Delete(article_ID.Text);
            Response.Redirect("EditDocment.aspx");
        }
    }
    protected void article_list_ItemEditing(object sender, ListViewEditEventArgs e)
    {
        article_list.EditIndex = e.NewEditIndex;
        article();
    }
    //Slēpjam nevajadzīgās article kolonnas
    protected void article_list_ItemDataBound(object sender, ListViewItemEventArgs e)
    {
        e.Item.FindControl("article_ID").Visible = false;
        e.Item.FindControl("document_ID").Visible = false;
    }

    protected void EditButton_Click(object sender, EventArgs e)
    {
        string id = lbl_doc_id.Text;
        string doc_numb = tb_doc_number.Text;
        string doc_date = tb_doc_date.Text;
        string doc_pay_date = tb_doc_pay_date.Text;
        string id_isu_user = lbl_user_id.Text;
        string id_company_by = Drop_company_from.SelectedItem.Value;
        string id_client_to = Drop_client_to.SelectedItem.Value;
        string id_document_type = Drop_doc_type.SelectedItem.Value;
        string id_payment_type = Drop_pay_type.SelectedItem.Value;
        string delivery_place = tb_delivery_place.Text;
        string address_company_by = Drop_address_company_from.SelectedItem.Value;
        string address_client_to = Drop_address_client_to.SelectedItem.Value;
        string bank_company_by = Drop_BANK_company_from.SelectedItem.Value;
        string bank_client_to = Drop_BANK_client_to.SelectedItem.Value;
        string account_company_by = Drop_ACCOUNT_company_from.SelectedItem.Value;
        string account_client_to = Drop_ACCOUNT_client_to.SelectedItem.Value;
        string statuss = "Active";
        
        _Invoice.Update(id, doc_numb, doc_date, doc_pay_date, id_isu_user, id_company_by, id_client_to, 
            id_document_type, id_payment_type, delivery_place, address_company_by, address_client_to,
                        bank_company_by, bank_client_to, account_company_by, account_client_to,statuss);

        Response.Redirect("MyInvoices.aspx");
    }
    protected void CancelButton_Click(object sender, EventArgs e)
    {
        Response.Redirect("MyInvoices.aspx");
    }

    protected void Drop_company_from_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_company_from.SelectedItem.Value == "--Select--")
        {
            Drop_address_company_from.Items.Clear();
            Drop_address_company_from.Items.Insert(0, "--Select--");
            Drop_BANK_company_from.Items.Clear();
            Drop_BANK_company_from.Items.Insert(0, "--Select--");
            Drop_ACCOUNT_company_from.Items.Clear();
            Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_address_company_from.Items.Clear();
            int compID = Convert.ToInt32(Drop_company_from.SelectedItem.Value);
            FillAddress_CompanyFrom(compID);

            Drop_BANK_company_from.Items.Clear();
            int compID1 = Convert.ToInt32(Drop_company_from.SelectedItem.Value);
            FillBanks_CompanyFrom(compID1);
        }
    }
    protected void Drop_BANK_company_from_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_BANK_company_from.SelectedItem.Value == "--Select--")
        {
            Drop_ACCOUNT_company_from.Items.Clear();
            Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_ACCOUNT_company_from.Items.Clear();
            int bankID = Convert.ToInt32(Drop_BANK_company_from.SelectedItem.Value);
            int compID1 = Convert.ToInt32(Drop_company_from.SelectedItem.Value);
            FillAccounts_CompanyFrom(bankID, compID1);
        }
    }
    private void FillAddress_CompanyFrom(int compID)
    {
        conn.Open();
        SqlCommand sel_company_by_address = new SqlCommand("SELECT id, address FROM company_address WHERE id_company=@company_id", conn);
        sel_company_by_address.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = compID;
        SqlDataReader rd = sel_company_by_address.ExecuteReader();
        while (rd.Read())
        {
            ListItem item = new ListItem();
            item.Value = rd["id"].ToString();
            item.Text = rd["address"].ToString();
            Drop_address_company_from.Items.Add(item);
        }
        rd.Close();
        Drop_address_company_from.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillBanks_CompanyFrom(int compID1)
    {
        List<int> bank = new List<int>();
        conn.Open();
        SqlCommand sel_company_by_bank = new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
        sel_company_by_bank.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = compID1;
        SqlDataReader rd = sel_company_by_bank.ExecuteReader();
        while (rd.Read())
        {
            bank.Add(Convert.ToInt32(rd["id_bank"]));
        }
        rd.Close();
        conn.Close();
        conn.Open();
        foreach (var bank_id in bank)
        {
            SqlCommand selCmd = new SqlCommand("SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bank_id;
            SqlDataReader rd1 = selCmd.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id"].ToString();
                item.Text = rd1["bank_name"].ToString();
                Drop_BANK_company_from.Items.Add(item);
            }
            rd1.Close();
        }
        Drop_BANK_company_from.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillAccounts_CompanyFrom(int bankID, int compID1)
    {
        List<int> company_bank_ID = new List<int>();
        conn.Open();
        SqlCommand sel_company_by_bank_ID = new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
        sel_company_by_bank_ID.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bankID;
        sel_company_by_bank_ID.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = compID1;
        SqlDataReader rd = sel_company_by_bank_ID.ExecuteReader();
        while (rd.Read())
        {
            company_bank_ID.Add(Convert.ToInt32(rd["id"]));
        }
        conn.Close();
        foreach (var com_bank_ID in company_bank_ID)
        {
            conn.Open();
            SqlCommand sel_company_by_bank_account = new SqlCommand("SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id", conn);
            sel_company_by_bank_account.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).Value = com_bank_ID;
            SqlDataReader rd1 = sel_company_by_bank_account.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id_account_number"].ToString();
                item.Text = rd1["account_number"].ToString();
                Drop_ACCOUNT_company_from.Items.Add(item);
            }
            conn.Close();
        }
        Drop_ACCOUNT_company_from.Items.Insert(0, "--Select--");
    }

    protected void Drop_client_to_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_client_to.SelectedItem.Value == "--Select--")
        {
            Drop_address_client_to.Items.Clear();
            Drop_address_client_to.Items.Insert(0, "--Select--");
            Drop_BANK_client_to.Items.Clear();
            Drop_BANK_client_to.Items.Insert(0, "--Select--");
            Drop_ACCOUNT_client_to.Items.Clear();
            Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_address_client_to.Items.Clear();
            int clientID = Convert.ToInt32(Drop_client_to.SelectedItem.Value);
            FillAddress_ClientTo(clientID);
            Drop_BANK_client_to.Items.Clear();
            int clientID1 = Convert.ToInt32(Drop_client_to.SelectedItem.Value);
            FillBanks_ClientTo(clientID1);
        }
    }
    protected void Drop_BANK_client_to_SelectedIndexChanged1(object sender, EventArgs e)
    {
        if (Drop_BANK_client_to.SelectedItem.Value == "--Select--")
        {
            Drop_ACCOUNT_client_to.Items.Clear();
            Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
        }
        else
        {
            Drop_ACCOUNT_client_to.Items.Clear();
            int client_bankID = Convert.ToInt32(Drop_BANK_client_to.SelectedItem.Value);
            int client_ID1 = Convert.ToInt32(Drop_client_to.SelectedItem.Value);
            FillAccounts_ClientTo(client_bankID, client_ID1);
        }
    }
    private void FillAddress_ClientTo(int clientID)
    {
        conn.Open();
        SqlCommand sel_client_to_address = new SqlCommand("SELECT id, address FROM company_address WHERE id_company=@company_id", conn);
        sel_client_to_address.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = clientID;
        SqlDataReader rd = sel_client_to_address.ExecuteReader();
        while (rd.Read())
        {
            ListItem item = new ListItem();
            item.Value = rd["id"].ToString();
            item.Text = rd["address"].ToString();
            Drop_address_client_to.Items.Add(item);
        }
        rd.Close();
        Drop_address_client_to.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillBanks_ClientTo(int clientID1)
    {
        List<int> bank = new List<int>();
        conn.Open();
        SqlCommand sel_client_to_bank = new SqlCommand("SELECT id_bank FROM company_bank WHERE id_company=@company_id GROUP BY id_bank", conn);
        sel_client_to_bank.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = clientID1;
        SqlDataReader rd = sel_client_to_bank.ExecuteReader();
        while (rd.Read())
        {
            bank.Add(Convert.ToInt32(rd["id_bank"]));
        }
        rd.Close();
        conn.Close();
        conn.Open();
        foreach (var bank_id in bank)
        {
            SqlCommand selCmd = new SqlCommand("SELECT id, bank_name FROM bank WHERE id=@bank_id GROUP BY id, bank_name", conn);
            selCmd.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = bank_id;
            SqlDataReader rd1 = selCmd.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id"].ToString();
                item.Text = rd1["bank_name"].ToString();
                Drop_BANK_client_to.Items.Add(item);
            }
            rd1.Close();
        }
        Drop_BANK_client_to.Items.Insert(0, "--Select--");
        conn.Close();
    }
    private void FillAccounts_ClientTo(int client_bankID, int client_ID1)
    {
        List<int> client_bank_ID = new List<int>();
        conn.Open();
        SqlCommand sel_client_bank_ID = new SqlCommand("SELECT id FROM company_bank WHERE id_bank=@bank_id AND id_company=@company_id", conn);
        sel_client_bank_ID.Parameters.Add(new SqlParameter("@bank_id", System.Data.SqlDbType.Int)).Value = client_bankID;
        sel_client_bank_ID.Parameters.Add(new SqlParameter("@company_id", System.Data.SqlDbType.Int)).Value = client_ID1;
        SqlDataReader rd = sel_client_bank_ID.ExecuteReader();
        while (rd.Read())
        {
            client_bank_ID.Add(Convert.ToInt32(rd["id"]));
        }
        conn.Close();
        foreach (var cl_bank_ID in client_bank_ID)
        {
            conn.Open();
            SqlCommand sel_client_to_bank_account = new SqlCommand("SELECT id_account_number, account_number FROM account_number WHERE id_company_bank=@company_bank_id ", conn);
            sel_client_to_bank_account.Parameters.Add(new SqlParameter("@company_bank_id", System.Data.SqlDbType.Int)).Value = cl_bank_ID;
            SqlDataReader rd1 = sel_client_to_bank_account.ExecuteReader();
            while (rd1.Read())
            {
                ListItem item = new ListItem();
                item.Value = rd1["id_account_number"].ToString();
                item.Text = rd1["account_number"].ToString();
                Drop_ACCOUNT_client_to.Items.Add(item);
            }
            conn.Close();
        }
        Drop_ACCOUNT_client_to.Items.Insert(0, "--Select--");
    }

    private List<int> number;
    protected void Drop_doc_type_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (Drop_doc_type.SelectedItem.ToString() != "--Select--")
        {
            string doc;
            string doc_type = Drop_doc_type.SelectedItem.ToString();
            if (doc_type == "Priekšapmaksas rēķins")
            {
                doc = doc_type.Substring(0, 2);
            }
            else
            {
                doc = doc_type.Substring(0, 1);
            }

            number = new List<int>();

            SqlCommand sCmd_doc_num = new SqlCommand("SELECT document_number FROM document WHERE document_number LIKE '" + doc.ToUpper() + "%' AND id_document_type=@document_type_id;", conn);
            sCmd_doc_num.Parameters.Add(new SqlParameter("@document_type_id", SqlDbType.Int)).Value = Drop_doc_type.SelectedValue;
            conn.Open();
            SqlDataReader dr = sCmd_doc_num.ExecuteReader();
            while (dr.Read())
            {
                string doc_num = dr["document_number"].ToString();
                if (doc_num.StartsWith("PR"))
                {
                    string num = doc_num.Substring(2, doc_num.Length - 7);
                    number.Add(Convert.ToInt32(num));
                }
                else
                {
                    string num = doc_num.Substring(1, doc_num.Length - 6);
                    number.Add(Convert.ToInt32(num));
                }
            }
            dr.Close();
            conn.Close();

            DateTime dt = DateTime.Now;
            string year = dt.Year.ToString();
            if (number.Count == 0)
            {
                number.Add(1);
                int maximum = number.Max();
                if (doc_type == "Priekšapmaksas rēķins")
                {
                    doc = doc_type.Substring(0, 2);
                    string date = year;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
                else
                {
                    doc = doc_type.Substring(0, 1);
                    string date = year;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
                number.Clear();
            }
            else
            {
                int maximum = number.Max();
                if (doc_type == "Priekšapmaksas rēķins")
                {
                    doc = doc_type.Substring(0, 2);
                    string date = year;
                    maximum++;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
                else
                {
                    doc = doc_type.Substring(0, 1);
                    string date = year;
                    maximum++;
                    tb_doc_number.Text = doc.ToUpper() + maximum + "/" + date;
                }
            }
        }
        else
        {
            tb_doc_number.Text = "";
        }
    }
}